
*******************************************************************
*********** Haircuts and Restructuring Analysis - Overview  *******
*******************************************************************

*This do file produces the  graphs and tables on sovereign restructurings and haircuts in Meyer, Reinhart and Trebesch "Sovereign Bonds since Waterloo" (QJE), in particular Figure 1, Figure 2 and Table 1

* Define format for output files 
	global format1 putexcel A1:Z200, fpattern(solid ,white  ,white) font(timesnewroman, 11) overwritefmt
	global format2 fpattern(solid ,white ,white) font(timesnewroman, 11)
		

* =================================================================
* Figure 1: Sovereign debt restructurings with foreign private creditors, 1815-2016
* =================================================================

* Load haircut data 
	use haircuts_full_sample, clear 

* Generate number of agreements by kind of restructuring
	gen counta = 1 
	collapse (sum)  counta, by(bond_restructuring year_agreement)
	tsset bond_restructuring year_agreement
	tsfill
	replace counta = 0 if counta ==. 
	
* Produce and export figure 1
	#delimit; 
	 twoway	
			(bar counta year_agreement if bond_restructuring == 1, cmissing(n)  color(gs4)) 
			(bar counta year_agreement if bond_restructuring == 0, cmissing(n) color(gs11)) 
			,
			ylabel(0 (2) 16, nogrid angle(0) labsize(medium)) 
			xlabel(1815 (9) 2015, angle(45)  labsize(medium)) 
			ytitle("Nr. of restructurings per year", size(medium))  
			xtitle("") xsize(7)
			graphregion(color(white)) 
			text(10 1896 "Bond era" "(pre-WW2)", size(medium))
			text(14 1968 "Bank loan era" "(1970s-" "mid-1990s)",  size(medium))
			text(8 2013  "Modern" "bond era" "(since" "mid-""1990s)", size(medium))
			legend( symysize(*.5) symxsize(*.5) region(color(white)) col(2) 
			label(2 "Bank debt restructurings") 
			label(1 "Bond restructurings") size(medium) order(2 1))
	;
	#delimit cr 

	graph export "Figure1.png", as(png) replace

* =================================================================
* Figure 2: Haircuts in sovereign debt restructurings with foreign private creditors since 1815
* =================================================================
* Load haircut data 
	use haircuts_final_sample, clear 


* Produce and export figure 2
	#delimit; 

	twoway 
		(scatter haircut_pv year_combined_deal, msymbol(i) mlabsize(tiny) mlabcolor(gs1) 
				mlabel(code) mlabposition(0) ) 
		(function y=0, range(1815 2015) clstyle(dash) clpattern(longdash)) 
		(scatter haircut_pv year_combined_deal [aw=debt_restructured_real], 
		msymbol(Oh) mcolor(gs0)  mlwidth(vthin)), 
		xlabel(1815(20)2015) ylabel( ,angle(0) nogrid) ytitle("") xtitle("") 
		graphregion(color(white)) legend(off)
	;
	#delimit cr 


	graph export "Figure2.png", as(png) replace

* =================================================================
* Table 1: Sovereign haircuts with foreign private creditors (1815-2016) 
* =================================================================
* Load haircut data 
	use haircuts_final_sample, clear 
	
mat M = J(11,6,.)

	local c=1
	quietly sum haircut_pv, det
	foreach stat in N mean p50 sd min max {
	mat M[2,`c'] = real(string(r(`stat'),"%4.0f"))  
	local c=`c'+1
	}

* Historical sample (defaults pre-1970, only bond restructurings occurred, no bank debt)
	local c=1
	quietly sum haircut_pv if case_historical == 1,det
	foreach stat in N mean p50 sd min max {
	mat M[3,`c'] = real(string(r(`stat'),"%4.0f"))  
	local c=`c'+1
	}
	
* Modern sample (defaults post-1970, incl. 152 bank debt defaults and 23 bond defaults)
	local c=1
	quietly sum haircut_pv if case_historical == 0,det
	foreach stat in N mean p50 sd min max {
	mat M[4,`c'] = real(string(r(`stat'),"%4.0f"))  
	local c=`c'+1
	}

* Modern subsample of 23 recent bond restructurings (note: first “modern” bond exchange is 1998)
	local c=1
	quietly sum haircut_pv if case_historical == 0 & (bond_restructuring == 1 | bankdebt_bond_restructuring == 1),det
	foreach stat in N mean p50 sd min max {
	mat M[5,`c'] = real(string(r(`stat'),"%4.0f"))  
	local c=`c'+1
	}


*Face value haircut
	local c=1
	quietly sum haircut_fv ,det
	foreach stat in N mean p50 sd min max {
	mat M[9,`c'] = real(string(r(`stat'),"%4.0f"))  
	local c=`c'+1
	}
	
* Weighted haircuts, by amount restructured
	collapse (count) cases= haircut_pv (mean) mean = haircut_pv (p50) median = haircut_pv ///
			(sd) sd =haircut_pv (min) min =haircut_pv (max) max =haircut_pv [aw=debt_restructured_real]

	local c=1
	foreach var of varlist cases mean median sd min max {
	mat M[8,`c'] = real(string(`var'[1],"%4.0f"))  
	local c=`c'+1
	}
	
* Export to excel 	
* ---------------------------------------------------------
* Define row names 	
	#delimit;
	local row `" "Haircuts across time (by default-restructuring event)" 
				"Full sample (1815-2016)"
				"Historical sample (defaults pre-1970, only bond restructurings occurred, no bank debt)"
				"Modern sample (defaults post-1970, incl. 152 bank debt defaults and 23 bond defaults)" 
				"	… subsample of 23 recent bond restructurings (note: first “modern” bond exchange is 1998)"
				""
				"Alternative haircut measures"
				"Weighted haircuts, by amount restructured"
				"Face value haircut" "' 		
	;
	#delimit cr
			
* Open output file	
	putexcel set "tables.xlsx", sheet("Table 1")  modify
	

* Export row names 	
	local i = 4
	foreach d of local row{ 								 
	putexcel B`i'=("`d'"), txtwrap
	local i = `i' +1
	}

* Export stats
	mat colnames M = "Cases" "Mean" "Median" "SD" "Min" "Max"	
	putexcel (C3) = matrix(M), colnames

* Format excel cells
	$format1 txtwrap
	putexcel B3:H3, border(top,double) $format2 overwritefmt 
	putexcel B4:H4, border(top) $format2 overwritefmt
	putexcel B14:H14, border(top,double) $format2 overwritefmt

	
	mata													
	b = xl()
	b.load_book("tables.xlsx")
	b.set_sheet("Table 1")
	b.set_column_width(2,2,20) //make row column widest
	b.close_book()
	end
	





